const { Sequelize } = require('sequelize')
const { SceneryModel, HotelModel, RestaurantModel, CityModel } = require('../../models/index')

// 景点
// 获取景点柱状图所需数据
const getSceneryBarChartData = async (req, res) => {
  try {
    // 使用sequelize里的group和count方法来获取按类型分类的景点数量
    const result = await SceneryModel.findAll({
      attributes: ['type', [Sequelize.fn('COUNT', Sequelize.col('scenery_id')), 'count']],
      group: ['type'],
      raw: true, //获取原始结果
      order: [['count', 'DESC']]
    })

    // 将结果转换为图表库可以理解的格式
    const chartData = result.map(item => {
      return {
        type: item.type,
        count: item.count
      }
    })

    // 返回成功响应
    res.status(200).json({
      title: '景点柱形图数据',
      data: chartData
    })
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
}

// 获取景点等级饼图所需数据
const getSceneryPieChartData = async (req, res) => {
  try {
    // 使用sequelize里的group和count方法来获取按类型分类的景点等级
    const result = await SceneryModel.findAll({
      attributes: ['grade', [Sequelize.fn('COUNT', Sequelize.col('scenery_id')), 'count']],
      group: ['grade'],
      raw: true, //获取原始结果
      order: [['count', 'DESC']]
    })

    // 将结果转化为图标库理解的格式
    const chartData = result.map(item => {
      return {
        type: item.grade,
        count: item.count
      }
    })

    // 返回成功响应
    res.status(200).json({
      title: '景点饼图数据',
      data: chartData
    })

  } catch (error) {
    res.status(500).json({ error: error.message })
  }
}

// 酒店
// 获取酒店柱状图所需数据
const getHotelBarChartData = async (req, res) => {
  try {
    // 使用sequelize里的group和count方法来获取按类型分类的酒店数量
    const result = await HotelModel.findAll({
      attributes: ['type', [Sequelize.fn('COUNT', Sequelize.col('hotel_id')), 'count']],
      group: ['type'],
      raw: true, //获取原始结果
      order: [['count', 'DESC']]
    })

    // 将结果转换为图表库可以理解的格式
    const chartData = result.map(item => {
      return {
        type: item.type,
        count: item.count
      }
    })

    // 返回成功响应
    res.status(200).json({
      title: '景点柱形图数据',
      data: chartData
    })
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
}

// 获取酒店等级饼图所需数据
const getHotelPieChartData = async (req, res) => {
  try {
    // 使用sequelize里的group和count方法来获取按类型分类的酒店等级
    const result = await HotelModel.findAll({
      attributes: ['grade', [Sequelize.fn('COUNT', Sequelize.col('hotel_id')), 'count']],
      group: ['grade'],
      raw: true, //获取原始结果
      order: [['count', 'DESC']]
    })

    // 将结果转化为图标库理解的格式
    const chartData = result.map(item => {
      return {
        type: item.grade,
        count: item.count
      }
    })

    // 返回成功响应
    res.status(200).json({
      title: '酒店饼图数据',
      data: chartData
    })

  } catch (error) {
    res.status(500).json({ error: error.message })
  }
}

// 餐厅
// 获取餐厅柱状图所需数据
const getRestaurantBarChartData = async (req, res) => {
  try {
    // 使用sequelize里的group和count方法来获取按类型分类的餐厅数量
    const result = await RestaurantModel.findAll({
      attributes: ['type', [Sequelize.fn('COUNT', Sequelize.col('restaurant_id')), 'count']],
      group: ['type'],
      raw: true, //获取原始结果
      order: [['count', 'DESC']]
    })

    // 将结果转换为图表库可以理解的格式
    const chartData = result.map(item => {
      return {
        type: item.type,
        count: item.count
      }
    })

    // 返回成功响应
    res.status(200).json({
      title: '景点柱形图数据',
      data: chartData
    })
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
}

// 计算平均值函数
const getAveragePrice = async (Model, cityAs) => {
  const results = await Model.findAll({
    include: [
      {
        model: CityModel,
        as: cityAs,
        attributes: ['name']
      }
    ],
    attributes: [[Sequelize.fn('AVG', Sequelize.col('price')), 'average'], cityAs + '.name'],
    group: [cityAs + '.name'],
    raw: true,
    order: [['average', 'DESC']]
  });

  return results.map(item => {
    return {
      city: item[cityAs + '.name'],
      average: parseFloat(item.average).toFixed(2)
    };
  });
};

// 获取各城市景点、酒店、餐厅平均价格折线图
const getCityLineChartData = async (req, res) => {
  try {
    const [sceneryResult, hotelResult, restaurantResult] = await Promise.all([
      getAveragePrice(SceneryModel, 'scenery_locatedCity'),
      getAveragePrice(HotelModel, 'hotel_locatedCity'),
      getAveragePrice(RestaurantModel, 'restaurant_locatedCity')
    ]);

    // 将三个结果数组合并为一个数组，每个对象包含所有三个平均价格
    const data = sceneryResult.map((scenery, index) => {
      const hotel = hotelResult.find(hotel => hotel.city === scenery.city);
      const restaurant = restaurantResult.find(restaurant => restaurant.city === scenery.city);
      return {
        city: scenery.city,
        sceneryAverage: scenery.average,
        hotelAverage: hotel ? hotel.average : 0,
        restaurantAverage: restaurant ? restaurant.average : 0
      };
    });

    res.status(200).json({
      title: '堆叠折线图数据',
      data: data
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
};

module.exports = {
  getSceneryBarChartData,
  getSceneryPieChartData,
  getHotelBarChartData,
  getHotelPieChartData,
  getRestaurantBarChartData,
  getCityLineChartData
}